import pymysql

mysql={
      "host":"localhost",
      "port":3306,
      "user":"root",
      "passwd":"123456",
      "database":"dict",
      "charset":"utf8"
}


class SqlLink():
    def __init__(self, kwargs):
        self.kwargs=kwargs
        self.db=pymysql.connect(**kwargs)
        self.cur = self._create_cur()

    def _create_cur(self):
        cur=self.db.cursor()
        return cur

    def inquere_user(self,name="",passwd=""):
        sql = "select * from user where binary name=%s and password=%s;"
        self.cur.execute(sql, [name, passwd])
        return self.cur.fetchone()

    def insert(self,name,passwd):
        sql="select name from user where binary name=%s"
        self.cur.execute(sql,[name])
        user=self.cur.fetchone()
        if user:
            return "bad"
        sql="insert into user (name,password) values (%s,%s);"
        self.cur.execute(sql,[name,passwd])
        self.db.commit()
        return "ok"

    def inquere(self,user_name,word):
        sql="select * from words where word=%s"
        self.cur.execute(sql, [word])
        data=self.cur.fetchone()
        if data:
            sql="insert into history (name,word) values (%s,%s);"
            self.cur.execute(sql, [user_name,word])
            self.db.commit()
            sql = "select count(word) from history where name=%s;"
            self.cur.execute(sql,[user_name])
            history_number = self.cur.fetchone()[0]
            if history_number > 10:
                sql = "select  id   from history where binary name=%s;"
                self.cur.execute(sql,[user_name])
                word_list = self.cur.fetchall()
                for i in range(history_number - 10):
                    delete_word = word_list[i][0]
                    delete_sql = "delete from history where id=%s;"
                    self.cur.execute(delete_sql, [delete_word])
                    self.db.commit()
            return data[2]


        else:
            return "bad"


    def inquere_history(self,user_name):
        sql = "select name,word,query_time from history where binary name=%s;"
        self.cur.execute(sql, [user_name])
        return self.cur.fetchall()




if __name__ == '__main__':
    db=SqlLink(mysql)
    db.inquere_user(name="ldkd",passwd="15654ffd")